import pymysql


class DBUtil(object):
    conn = None

    @classmethod
    def __get_conn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(host="211.103.136.244", port=7061,
                                       user="student",password="iHRM_student_2021",
                                       database="ihrm", charset="utf8")
        return cls.conn

    @classmethod
    def __close_conn(cls):
        if cls.conn:
            cls.conn.close()
            cls.conn = None

    @classmethod
    def get_one(cls, sql):
        cursor = None
        res = None
        try:
            cursor = cls.__get_conn().cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("执行查询sql异常：", str(err))
            raise err
        finally:
            cursor.close()
            cls.__close_conn()
            return res

    @classmethod
    def uid_db(cls, sql):
        # 执行增删改sql操作
        cursor = None
        res = None
        try:
            cursor = cls.__get_conn().cursor()  # 获取游标
            cursor.execute(sql)  # 执行语句
            res = cursor.rowcount  # 提交事务
            cls.conn.commit()
        except Exception as err:
            print("执行增删改sql异常：", str(err))
            cls.conn.rollback()
            raise err
        finally:
            cursor.close()
            cls.__close_conn()
            return res


if __name__ == '__main__':
    # sql_query = "select * from bs_user where mobile = '13800000002'"
    # res = DBUtil.get_one(sql_query)
    # print(res)

    sql_query = "insert into bs_user(id, mobile, username) value('1451882525755441152','15279655808','TOM')"
    res = DBUtil.uid_db(sql_query)
    print(res)

    # sql_del = "delete from bs_user where id = '111177778888'"
    # res = DBUtil.uid_db(sql_del)
    # print("影响的行数:", res)
